package com.portalEsporteClubeBahia.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.portalEsporteClubeBahia.app.ApplicationException;
import com.portalEsporteClubeBahia.app.vo.StadiumVO;

public class StadiumDAO extends DAOFactory {

	public StadiumDAO() throws ApplicationException {
		super();
	}
	
	public List<StadiumVO> findAll() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<StadiumVO> list = new ArrayList<StadiumVO>();
		
		try{
			
			StringBuffer sql = new StringBuffer(" SELECT ID, NAME FROM TBL_STADIUM ORDER BY NAME ");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				StadiumVO stadium = new StadiumVO();
				stadium.setId(rs.getInt(1));
				stadium.setName(rs.getString(2));
				list.add(stadium);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public List<StadiumVO> findByFilter(StadiumVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<StadiumVO> list = new ArrayList<StadiumVO>();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME FROM TBL_STADIUM ");
		sql.append(" WHERE 1=1 ");
		if(filter.getName()!=null && !filter.getName().equalsIgnoreCase(""))
			sql.append(" AND NAME LIKE '%"+filter.getName()+"%' ");
		sql.append(" ORDER BY NAME ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				StadiumVO stadium = new StadiumVO();
				stadium.setId(rs.getInt("ID"));
				stadium.setName(rs.getString("NAME"));
				list.add(stadium);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public void insert(StadiumVO stadium) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("INSERT INTO TBL_STADIUM (NAME) VALUES (?) ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, stadium.getName());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(StadiumVO stadium) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_STADIUM SET NAME=? WHERE ID =? ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, stadium.getName());
			ps.setInt(2, stadium.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(StadiumVO stadium) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("DELETE FROM TBL_STADIUM WHERE ID =? ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, stadium.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}

	public StadiumVO findByPrimaryKey(StadiumVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StadiumVO stadium = new StadiumVO();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME FROM TBL_STADIUM WHERE ID = ?");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, filter.getId());
			rs = ps.executeQuery();
			
			while(rs.next()){
				stadium.setId(rs.getInt("ID"));
				stadium.setName(rs.getString("NAME"));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return stadium;
		
	}
}
